#! /usr/bin/env python
#coding=utf-8

import os

from processes import ProcessMgr
from smaps import VirtualMemroyArea

class MemDbBuilder(ProcessMgr):
	def __init__(self, cursor, mem_dir):
		ProcessMgr.__init__(self, mem_dir)

		self._cursor = cursor

		self._create_empty_table()

		self._add_processes()


	def _add_objects(self):
		sizes_keys = ", ".join(VirtualMemroyArea.VMA_SIZE_KEYS)
		for sobj in self.get_all_objects():
			sizes = ",".join([str(sobj[k]) for k in VirtualMemroyArea.VMA_SIZE_KEYS])
			sqlcmd = 'insert into objects(id, name, processes, vmas, %s) values (%d,"%s",%d,%d,%s)' % (sizes_keys, sobj["id"], sobj["name"], sobj["processes"], sobj["vmas"], sizes)

			self._cursor.execute(sqlcmd)

	def __add_object_vmas(self, proc, sobj, obj):
		sizes_keys = VirtualMemroyArea.VMA_SIZE_KEYS
		str_keys = VirtualMemroyArea.VMA_ADDR_KEYS + ("VmFlags", )
		all_keys = ", ".join(("id", "smap_id", "pid", "object_id") + sizes_keys + str_keys)
		for vma in obj.get_all():
			sizes = ",".join([str(vma[k]) for k in sizes_keys])
			strVals = ",".join(['"' + str(vma[k]) + '"' for k in str_keys])

			self._vmas_idx = self._vmas_idx + 1
			sqlcmd = 'insert into vmas(%s) values (%d,%d,%d,%d,%s,%s)' % (all_keys, self._vmas_idx, self._smaps_idx, proc["pid"], sobj["id"], sizes, strVals)

			self._cursor.execute(sqlcmd)

	def __add_process_smaps(self, proc):
		all_keys = ", ".join(("id", "pid", "object_id", "vmas") + VirtualMemroyArea.VMA_SIZE_KEYS)
		for obj in proc.get_all():
			self._smaps_idx = self._smaps_idx + 1
			sizes = ",".join([str(obj[k]) for k in VirtualMemroyArea.VMA_SIZE_KEYS])
			sobj = self.get_object_by_name(obj["name"])
			sqlcmd = 'insert into smaps(%s) values (%d,%d,%d,%d,%s)' % (all_keys, self._smaps_idx, proc["pid"], sobj["id"], len(obj.get_all()), sizes)
			self._cursor.execute(sqlcmd)

			self.__add_object_vmas(proc, sobj, obj)

	def __add_process_threads(self, proc):
		for obj in proc["threads"]:
			sqlcmd = 'insert into threads values (%d,"%s",%d)' % (obj["tid"], obj["name"], proc["pid"])
			self._cursor.execute(sqlcmd)

	def _add_processes(self):
		self._smaps_idx = 0
		self._vmas_idx = 0
		sizes_keys = ", ".join(VirtualMemroyArea.VMA_SIZE_KEYS)
		for proc in self.get_all():
			sizes = ",".join([str(proc[k]) for k in VirtualMemroyArea.VMA_SIZE_KEYS])
			sqlcmd = 'insert into processes(pid, ppid, threads, category, user, name, objects, vmas, %s) values (%d,%d,%d,"%s","%s","%s",%d,%d,%s)' % (sizes_keys, proc["pid"], proc["ppid"], len(proc["threads"]), proc["category"], proc["user"], proc["name"], proc["objects"], proc["vmas"], sizes)
			#print(sqlcmd)
			self._cursor.execute(sqlcmd)

			self.__add_process_smaps(proc)
			self.__add_process_threads(proc)

		self._add_objects()

	def _create_empty_table(self):
		self._cursor.execute("drop table if exists processes")
		self._cursor.execute("drop table if exists objects")
		self._cursor.execute("drop table if exists smaps")
		self._cursor.execute("drop table if exists vmas")
		self._cursor.execute("drop view if exists smaps_details")
		self._cursor.execute("drop view if exists vmas_details")
		self._cursor.execute("drop table if exists threads")
		self._cursor.execute("drop view if exists threads_details")
		self._cursor.execute("drop view if exists named_threads")

		sizes_keys = ", ".join(VirtualMemroyArea.VMA_SIZE_KEYS)

		self._cursor.execute("create table processes(pid INTEGER PRIMARY KEY, ppid, threads, category, user, name, objects, vmas, %s)" % sizes_keys)
		self._cursor.execute("create table objects(id INTEGER PRIMARY KEY, name, processes, vmas, %s)" % sizes_keys)
		self._cursor.execute("create table threads(tid INTEGER PRIMARY KEY, name, pid)")
		sqlcmd = "create view [threads_details] as select processes.pid as pid, processes.name as process, threads.tid as tid, threads.name as thread from threads left outer join processes on processes.pid = threads.pid"
		self._cursor.execute(sqlcmd)

		sqlcmd = "create view [named_threads] as select threads.name, count(distinct(threads.pid)) as processes, count(threads.tid) as threads from threads group by name"
		self._cursor.execute(sqlcmd)

		object_keys = ("id INTEGER PRIMARY KEY", "pid", "object_id", "vmas") + VirtualMemroyArea.VMA_SIZE_KEYS + \
					  ("FOREIGN KEY(pid) REFERENCES processes(pid)", "FOREIGN KEY(object_id) REFERENCES objects(id)")
		keys = ", ".join(object_keys)
		self._cursor.execute("create table smaps(%s)" % keys)

		vmas_keys = ("id INTEGER PRIMARY KEY", "smap_id", "pid", "object_id") + VirtualMemroyArea.VMA_ADDR_KEYS + VirtualMemroyArea.VMA_SIZE_KEYS + \
					("VmFlags", "FOREIGN KEY(smap_id) REFERENCES smaps(id)", "FOREIGN KEY(pid) REFERENCES processes(pid)", "FOREIGN KEY(object_id) REFERENCES objects(id)")
		keys = ", ".join(vmas_keys)
		self._cursor.execute("create table vmas(%s)" % keys)

		size_keys = ", ".join(["smaps.%s as %s" % (k, k) for k in VirtualMemroyArea.VMA_SIZE_KEYS])
		sqlcmd = "create view [smaps_details] as select smaps.id as id, proc_map.name as process, smaps.pid as pid, proc_map.ppid as ppid, proc_map.threads as threads, proc_map.category as category, proc_map.objects as objects, obj_map.name as name, smaps.object_id as object_id, obj_map.processes as processes, smaps.vmas as vmas, %s from smaps left outer join objects obj_map on obj_map.id = smaps.object_id left outer join processes proc_map on proc_map.pid = smaps.pid" % size_keys
		self._cursor.execute(sqlcmd)

		size_keys = ", ".join(["vmas.%s as %s" % (k, k) for k in VirtualMemroyArea.VMA_ADDR_KEYS + VirtualMemroyArea.VMA_SIZE_KEYS])
		sqlcmd = "create view [vmas_details] as select vmas.id as id, vmas.smap_id as smap_id, proc_map.name as process, vmas.pid as pid, proc_map.ppid as ppid, proc_map.objects as objects, obj_map.name as name, vmas.object_id as object_id, obj_map.processes as processes, %s from vmas left outer join objects obj_map on obj_map.id = vmas.object_id left outer join processes proc_map on proc_map.pid = vmas.pid" % size_keys
		self._cursor.execute(sqlcmd)


if __name__ == "__main__":
	import sqlite3
	# conn = sqlite3.connect("symdb.db")
	conn = sqlite3.connect("/Users/handy/Documents/work/archinfo/assets/db/aln/1119/archinfo.db")
	cursor = conn.cursor()

	builder = MemDbBuilder(cursor, "./aln")

	conn.commit()

	cursor.close()
	conn.close()

